/*******************************************************************************
 * LocationCategoriesServiceDelegator.java
 *
 * Created by Nishan
 * Last modified Date: Oct 20, 2008 3:46:14 PM by nishan
  * Version : 0.1
 * Copyright (c) 2008 - 2009 cyberThink Infotech Pvt. Ltd., All rights reserved.
 ******************************************************************************/

package com.printeverywhere.delegator;

import static com.printeverywhere.util.AdminConstants.ALL_THE_SEQUENCES_UPDATED;
import static com.printeverywhere.util.AdminConstants.AS;
import static com.printeverywhere.util.AdminConstants.BLANK;
import static com.printeverywhere.util.AdminConstants.CALLED_ADD_METHOD;
import static com.printeverywhere.util.AdminConstants.CALLED_DELETE_METHOD;
import static com.printeverywhere.util.AdminConstants.CALLED_UPDATE_METHOD;
import static com.printeverywhere.util.AdminConstants.CATEGORYID;
import static com.printeverywhere.util.AdminConstants.CATEGORYNAME_EQULAS;
import static com.printeverywhere.util.AdminConstants.COMMA;
import static com.printeverywhere.util.AdminConstants.DOT;
import static com.printeverywhere.util.AdminConstants.EQUALS;
import static com.printeverywhere.util.AdminConstants.ERROR_GET_LIST_WITHOUT_SEARCH_CRITERIA;
import static com.printeverywhere.util.AdminConstants.ERROR_IN_ADDING_THE_RECORD;
import static com.printeverywhere.util.AdminConstants.ERROR_IN_DELETING_THE_RECORD;
import static com.printeverywhere.util.AdminConstants.ERROR_IN_UPDATING_THE_RECORD;
import static com.printeverywhere.util.AdminConstants.GET_LIST_WITHOUT_SEARCH_CRITERIA;
import static com.printeverywhere.util.AdminConstants.GET_LIST_WITH_SEARCH_CRITERIA;
import static com.printeverywhere.util.AdminConstants.GET_LIST_WITH_SEARCH_LIST_CRITERIA;
import static com.printeverywhere.util.AdminConstants.LEV;
import static com.printeverywhere.util.AdminConstants.NONE;
import static com.printeverywhere.util.AdminConstants.ON;
import static com.printeverywhere.util.AdminConstants.PARENTCATEGORYID;
import static com.printeverywhere.util.AdminConstants.QUOTE_AND;
import static com.printeverywhere.util.AdminConstants.QUOTE_SEMICOLON;
import static com.printeverywhere.util.AdminConstants.RECORD_ADDED;
import static com.printeverywhere.util.AdminConstants.RECORD_DELETED;
import static com.printeverywhere.util.AdminConstants.RECORD_UPDATED;
import static com.printeverywhere.util.AdminConstants.SELECT;
import static com.printeverywhere.util.AdminConstants.SESSION_CLOSED;
import static com.printeverywhere.util.AdminConstants.T;
import static com.printeverywhere.util.AttributeConstants.CLOSING_SESSION;
import static com.printeverywhere.util.LoggerConstants.USERS_COUNT;
import static com.printeverywhere.util.LoggerConstants.USERS_COUNT_EXCEPTION;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.opensymphony.xwork2.ActionContext;
import com.printeverywhere.action.LocationCategoriesAction;
import com.printeverywhere.dto.GeneralCategories;
import com.printeverywhere.dto.LocationCategories;
import com.printeverywhere.dto.LocationCategoriesWOS;
import com.printeverywhere.util.AdminConstants;
import com.printeverywhere.util.AttributeConstants;
import com.printeverywhere.util.HibernateUtil;
import com.printeverywhere.util.SearchCriteria;
import com.printeverywhere.util.SearchListCriteria;

/**
 * LocationCategoriesServiceDelegator class list all the location categories by
 * continent user can go through the country and state hierarchy. User can
 * update the Names all the locationCategories simultaneously. Any specific
 * location category is modified/deleted. deleting any parentCategory will cause
 * all its children to be deleted.
 *
 * @author punam
 */

public class LocationCategoriesServiceDelegator {

	/** The Constant for GET_MAX_SEQUENCE_OF_CHILD_QUERY. */
	private static final String GET_MAX_SEQUENCE_OF_CHILD_QUERY = "select max(sequence) from location_categories where categoryId != ? AND parentcategoryid=?"
			.intern();

	private static final String GET_MAX_SEQUENCE = "select max(sequence) from location_categories"
		.intern();

	/** The Constant GET_PARENT_QUERY. */
	private static final String GET_PARENT_QUERY = "select sequence from LocationCategories where categoryid="
			.intern();

	/** The Constant UPDATE_ALL_SEQUENCES. */
	private static final String UPDATE_ALL_SEQUENCES = "update LocationCategories set sequence = sequence + 1 Where sequence >= "
			.intern();

	/** The Constant GET_LEVEL_FOR_GIVEN_CATEOGRY. */
	private static final String GET_LEVEL_FOR_GIVEN_CATEOGRY = "select level from location_categories where categoryid = ?"
			.intern();

	/** The Constant DELETE_QUERY. */
	private static final String DELETE_QUERY = "delete from LocationCategories where mainparentid="
			.intern();

	/** The Constant DELETE_SUB_QUERY. */
	private static final String DELETE_SUB_QUERY = "or parentcategoryid= "
			.intern();

	/** The Constant DELETE_SUB_QUERY_CATEGORY. */
	private static final String DELETE_SUB_QUERY_CATEGORY = " or categoryid="
			.intern();

	/** The Constant SELECT_FOR_DELETE. */
	private static final String SELECT_FOR_DELETE = " FROM LocationCategories where mainparentid= "
			.intern();

	/** The Constant BREAD_CRUMB_QUERY1. */
	private static final String BREAD_CRUMB_QUERY1 = " FROM location_categories AS t1 "
			.intern();

	/** The Constant BREAD_CRUMB_QUERY2. */
	private static final String BREAD_CRUMB_QUERY2 = " LEFT JOIN location_categories AS t"
			.intern();

	/** The Constant BREAD_CRUMB_QUERY3. */
	private static final String BREAD_CRUMB_QUERY3 = " WHERE t1.categoryname = '"
			.intern();

	/** The logger. */
	private static Logger logger = Logger
			.getLogger(LocationCategoriesServiceDelegator.class);

	/**
	 * Gets the list of all the records from LocationCategories table without
	 * checking any predefined criteria, It simply list all the rows from
	 * database table.
	 *
	 * @return the list
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static List<LocationCategories> getList() throws HibernateException {
		logger.info(GET_LIST_WITHOUT_SEARCH_CRITERIA);
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		try {
			List<LocationCategories> list = session.createCriteria(
					LocationCategories.class).list();
			transaction.commit();
			return list;
		} catch (HibernateException e) {
			logger.error(ERROR_GET_LIST_WITHOUT_SEARCH_CRITERIA
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
	}

	/**
	 * Gets the count for Location categories.
	 *
	 * @return the count
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 *
	 * @author ankur
	 */
	public static Integer getCount() throws HibernateException {
		if (logger.isInfoEnabled()) {
			logger.info(USERS_COUNT);
		}
		Session session = HibernateUtil.getSession();
		Criteria criteria = session.createCriteria(LocationCategories.class);
		criteria.setProjection(Projections.rowCount());
		Transaction transaction = session.beginTransaction();
		List list;
		try {
			list = criteria.list();
			transaction.commit();
			if (list.size() > 0)
				return (Integer) list.get(0);
			else
				return 0;
		} catch (HibernateException e) {
			logger.error(USERS_COUNT_EXCEPTION);
			throw e;
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info(AttributeConstants.CLOSING_SESSION);
			}
			session.flush();
			HibernateUtil.closeSession();
		}
	}

	/**
	 * Append the locationCategories object as new row in locationCategories
	 * table.
	 *
	 * @param locationCategories
	 *            the location categories
	 *
	 * @return the string
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static String add(LocationCategories locationCategories)
			throws HibernateException {
		logger.info(CALLED_ADD_METHOD);
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		try {
			session.save(locationCategories);
			transaction.commit();
			logger.info(RECORD_ADDED);
			return RECORD_ADDED;
		} catch (HibernateException e) {
			logger.error(ERROR_IN_ADDING_THE_RECORD);
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
	}

	/**
	 * Gets the max id.
	 *
	 * @return the max id
	 */
	public static int getMaxSequence() {
		logger.info("Get maximum of sequece."
				.intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		int sequence = 0;
		try {
			Query query = session
					.createSQLQuery(GET_MAX_SEQUENCE);
			Object[] objects = query.list().toArray();
			if (objects.length > 0) {
				if (objects[0] != null) {
					sequence = ((Integer) objects[0]);
				}
			} else {
				sequence = 0;
			}
			transaction.commit();
		} catch (HibernateException e) {
			logger
					.error("Error in Get maximum of sequece."
							.intern());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return sequence;
	}

	/**
	 * Update the given modified locationCategories object to the old object.
	 *
	 * @param locationCategories
	 *            the location categories
	 *
	 * @return the string
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static String update(LocationCategories locationCategories)
			throws HibernateException {
		logger.info(CALLED_UPDATE_METHOD);
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		try {
			session.update(locationCategories);
			transaction.commit();
			logger.info(RECORD_UPDATED);
			return RECORD_UPDATED;
		} catch (HibernateException e) {
			logger.error(ERROR_IN_UPDATING_THE_RECORD);
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
	}

	/**
	 * Get maximum of sequece for the children of given parentId.
	 *
	 * @param categoryId
	 *            the category id
	 *
	 * @return the max of child sequence
	 */
	public static int getMaxOfChildSequence(int categoryId, int parentCategoryId) {
		logger.info("Get maximum of sequece for the children of given parentId"
				.intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		int sequence = 0;
		try {
			Query query = session
					.createSQLQuery(GET_MAX_SEQUENCE_OF_CHILD_QUERY);
			query.setInteger(0, categoryId);
			query.setInteger(1, parentCategoryId);
			Object[] objects = query.list().toArray();
			if (objects.length > 0) {
				if (objects[0] != null) {
					sequence = ((Integer) objects[0]);
				}
			} else {
				sequence = 0;
			}
			transaction.commit();
		} catch (HibernateException e) {
			logger
					.error("Error in Get maximum of sequece for the children of givem parentId"
							.intern());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return sequence;
	}

	/**
	 * Gets the parent sequence from the locationCategories where the categoryId
	 * matches for the given categoryId.
	 *
	 * @param categoryId
	 *            the category id
	 *
	 * @return the parent sequence
	 */
	public static int getParentSequence(int categoryId) {
		logger.info("Called getParentSequence method.".intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		int sequence = 0;
		try {
			Query query = session.createQuery(GET_PARENT_QUERY + categoryId);
			Object[] objects = query.list().toArray();
			if (objects[0] != null) {
				sequence = (Integer) objects[0];
			}
			transaction.commit();
		} catch (HibernateException e) {
			logger
					.error("Error in getting the parent sequence of given categoryId "
							.intern()
							+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return sequence;
	}

	/**
	 * Update the sequence values by increasing all the sequence by 1, starting
	 * from given sequence value.it will modify all the sequence number for
	 * updating value of currently updated locationCategories object.
	 *
	 * @param sequence
	 *            the sequence
	 *
	 * @return the string
	 */
	public static String updateSequences(int sequence) {
		logger.info("Called updateSequences method".intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		try {
			Query query = session.createQuery(UPDATE_ALL_SEQUENCES + sequence);
			query.executeUpdate();
			transaction.commit();
		} catch (HibernateException e) {
			logger.error("Error in updating the sequence values ".intern()
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return ALL_THE_SEQUENCES_UPDATED;
	}

	/**
	 * Gets the level field value from locationCategories table where the
	 * categoryId matches given categoryId.
	 *
	 * @param categoryId
	 *            the category id
	 *
	 * @return the level for given category id
	 */
	public static int getLevelForGivenCategoryId(int categoryId) {
 		logger.info("Called method getLevelForGivenCategoryId.".intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		int level = 0;
		try {
			Query query = session.createSQLQuery(GET_LEVEL_FOR_GIVEN_CATEOGRY);
			query.setInteger(0, categoryId);
			Object[] objects = query.list().toArray();
			if (objects.length > 0) {
				if (objects[0] != null) {
					level = (Integer) objects[0];
				}
			} else {
				level = 0;
			}
			transaction.commit();
		} catch (HibernateException e) {
			logger.error("Error in getting the level for given CategoryId "
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return level;
	}

	/**
	 * Update level main parent id.
	 *
	 * @param level
	 *            the level
	 * @param mainParentId
	 *            the main parent id
	 *
	 * @return the string
	 */
	public static String updateLevelMainParentId(int level, int mainParentId) {
		return NONE;
	}

	/**
	 * Deletes all the LocationCategories where the categoryId, parentCategoryId
	 * or mainParentId matches with given id.
	 *
	 * @param id
	 *            the id
	 *
	 * @return the string
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static String delete(int id) throws HibernateException {
		logger.info(CALLED_DELETE_METHOD);
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		try {
			Query query = session.createQuery(DELETE_QUERY + id
					+ DELETE_SUB_QUERY + id + DELETE_SUB_QUERY_CATEGORY + id);
			query.executeUpdate();
			transaction.commit();
			logger.info(RECORD_DELETED);
			return RECORD_DELETED;
		} catch (HibernateException e) {
			logger.error(ERROR_IN_DELETING_THE_RECORD);
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
	}

	/**
	 * Gets the LocationCategories object where categoryId value matches with
	 * given id.
	 *
	 * @param categoryId
	 *            the category id
	 *
	 * @return the location categories
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static LocationCategories get(int categoryId)
			throws HibernateException {
		logger.info("Called get method".intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		LocationCategories locationCategories = new LocationCategories();
		try {
			Criteria criteria = session
					.createCriteria(LocationCategories.class).add(
							Expression
									.eq(AdminConstants.CATEGORYID, categoryId));
			List<LocationCategories> list = criteria.list();
			locationCategories = (list.size() > 0) ? list.get(0)
					: new LocationCategories();
			transaction.commit();

		} catch (HibernateException e) {

			logger.info("Error in getting the object for given categoryId "
					.intern()
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			HibernateUtil.closeSession();
		}
		return locationCategories;
	}

	/**
	 * Gets the LocationCategories object where categoryId value matches with
	 * given id.
	 *
	 * @param categoryId
	 *            the category id
	 *
	 * @return the location categories
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static LocationCategoriesWOS getWOS(int categoryId)
			throws HibernateException {
		logger.info("Called get method".intern());
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		LocationCategoriesWOS locationCategories = new LocationCategoriesWOS();
		try {
			Criteria criteria = session
					.createCriteria(LocationCategoriesWOS.class).add(
							Expression
									.eq(AdminConstants.CATEGORYID, categoryId));
			List<LocationCategoriesWOS> list = criteria.list();
			locationCategories = (list.size() > 0) ? list.get(0)
					: new LocationCategoriesWOS();
			transaction.commit();

		} catch (HibernateException e) {

			logger.info("Error in getting the object for given categoryId "
					.intern()
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.flush();
			HibernateUtil.closeSession();
		}
		return locationCategories;
	}

	/**
	 * Gets the LocationCategories list where id parameter matches for
	 * categoryId, parentCategoryId or mainParentId if any of the value matches
	 * the object is returned in the list for confirming deletion.
	 *
	 * @param id
	 *            the id
	 *
	 * @return the categories to delete
	 */
	public static List<LocationCategories> getCategoriesToDelete(int id) {
		logger.info("Called getCategoriesToDelete method.");
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		List<LocationCategories> locationCategoriesList = new ArrayList<LocationCategories>();
		try {
			Query query = session.createQuery(SELECT_FOR_DELETE + id
					+ DELETE_SUB_QUERY + id + DELETE_SUB_QUERY_CATEGORY + id);
			locationCategoriesList = query.list();
			transaction.commit();
		} catch (HibernateException e) {
			logger.error("Error in getting categories got given id "
					+ e.getMessage());
			transaction.rollback();
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.close();
		}
		return locationCategoriesList;
	}

	/**
	 * Gets the list of the LocationCategories objects based on the keys and
	 * values specified. It returns the list iff the condition in given criteria
	 * satisfies. For Ex: if it requires list for only those record with given
	 * parentCategoryId, it looks for the given searching criteria and retrieves
	 * list accordingly.
	 *
	 * @param searchCriteria
	 *            the search criteria
	 *
	 * @return the list
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static List<LocationCategories> getList(SearchCriteria searchCriteria)
			throws HibernateException {
		logger.info(GET_LIST_WITH_SEARCH_CRITERIA);
		Session session = HibernateUtil.getSession();
		Criteria criteria = session.createCriteria(LocationCategories.class);
		String orderBY = searchCriteria.getOrderBy();
		if (orderBY != null) {
			if (searchCriteria.isAsc()) {
				criteria.addOrder(Order.asc(orderBY));
			} else {
				criteria.addOrder(Order.desc(orderBY));
			}
		}
		Set<String> keys = searchCriteria.getAttributeNames();
		Iterator<String> it = keys.iterator();
		String key = null;
		Object value = null;
		while (it.hasNext()) {
			key = it.next();
			value = searchCriteria.getAttribute(key);
			criteria.add(Restrictions.eq(key, value));
		}
		Transaction transaction = session.beginTransaction();
		List<LocationCategories> list = new ArrayList<LocationCategories>();

		try {
			list = criteria.list();
			transaction.commit();
		} catch (HibernateException e) {
			for (String keyexception : keys) {
				logger.error(keys);
			}
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.flush();
			HibernateUtil.closeSession();
		}
		return list;
	}

	/**
	 * Gets the country list by region id.
	 *
	 * @param categoryId the category id
	 *
	 * @return the country list by region id
	 *
	 * @throws HibernateException the hibernate exception
	 */
	public static List<LocationCategories> getCountryListByRegionId(int categoryId) throws HibernateException {
		if(logger.isInfoEnabled()){
			logger.info("Getting Country list".intern());
		}
		Session session = HibernateUtil.getSession();
		Transaction transaction = null;
		try{
			transaction = session.beginTransaction();
			Criteria criteria = session.createCriteria(LocationCategories.class);
			criteria.add(Restrictions.eq("locationCategories.categoryId" ,categoryId));
			criteria.add(Restrictions.neProperty("locationCategories.categoryId" ,"categoryId" ));
			List<LocationCategories> countryList =  criteria.list();
			transaction.commit();
			return countryList;
		}catch (RuntimeException e) {
			if(transaction != null) transaction.rollback();
			logger.error("Got exception when getting Country list".intern());
			throw e;
		} finally {
			if(logger.isInfoEnabled()){
				logger.info(CLOSING_SESSION);
			}
			session.flush();
			session.close();
		}
	}

	/**
	 * Gets the country list by region id.
	 *
	 * @param categoryId the category id
	 *
	 * @return the country list by region id
	 *
	 * @throws HibernateException the hibernate exception
	 */
	public static List<LocationCategoriesWOS> getCountryListByRegionIdWOS(int categoryId) throws HibernateException {
		if(logger.isInfoEnabled()){
			logger.info("Getting Country list".intern());
		}
		Session session = HibernateUtil.getSession();
		Transaction transaction = null;
		try{
			transaction = session.beginTransaction();
			Criteria criteria = session.createCriteria(LocationCategoriesWOS.class);
			criteria.add(Restrictions.eq("locationCategories.categoryId" ,categoryId));
			criteria.add(Restrictions.neProperty("locationCategories.categoryId" ,"categoryId" ));
			List<LocationCategoriesWOS> countryList =  criteria.list();
			transaction.commit();
			return countryList;
		}catch (RuntimeException e) {
			if(transaction != null) transaction.rollback();
			logger.error("Got exception when getting Country list".intern());
			throw e;
		} finally {
			if(logger.isInfoEnabled()){
				logger.info(CLOSING_SESSION);
			}
			session.flush();
			session.close();
		}
	}

	/**
	 * Gets the state list by country id.
	 *
	 * @param countryId the country id
	 *
	 * @return the state list by country id
	 *
	 * @throws HibernateException the hibernate exception
	 */
	@SuppressWarnings("unchecked")
	public static List<LocationCategories> getStateListByCountryId(int categoryId) throws HibernateException {
		if(logger.isInfoEnabled()){
			logger.info("Getting State list".intern());
		}
		Session session = HibernateUtil.getSession();
		Transaction transaction = null;
		try{
			transaction = session.beginTransaction();
			Criteria criteria = session.createCriteria(LocationCategories.class);
			criteria.add(Restrictions.eq("locationCategories.categoryId" ,categoryId));
			List<LocationCategories> stateList =  criteria.list();
			transaction.commit();
			return stateList;
		}catch (RuntimeException e) {
			if(transaction != null) transaction.rollback();
			logger.error("Got exception when getting State list".intern());
			throw e;
		} finally {
			if(logger.isInfoEnabled()){
				logger.info(CLOSING_SESSION);
			}
			session.flush();
			session.close();
		}
	}

	/**
	 * Gets the state list by country id.
	 *
	 * @param countryId the country id
	 *
	 * @return the state list by country id
	 *
	 * @throws HibernateException the hibernate exception
	 */
	@SuppressWarnings("unchecked")
	public static List<LocationCategoriesWOS> getStateListByCountryIdWOS(int categoryId) throws HibernateException {
		if(logger.isInfoEnabled()){
			logger.info("Getting State list".intern());
		}
		Session session = HibernateUtil.getSession();
		Transaction transaction = null;
		try{
			transaction = session.beginTransaction();
			Criteria criteria = session.createCriteria(LocationCategoriesWOS.class);
			criteria.add(Restrictions.eq("locationCategories.categoryId" ,categoryId));
			List<LocationCategoriesWOS> stateList =  criteria.list();
			transaction.commit();
			return stateList;
		}catch (RuntimeException e) {
			if(transaction != null) transaction.rollback();
			logger.error("Got exception when getting State list".intern());
			throw e;
		} finally {
			if(logger.isInfoEnabled()){
				logger.info(CLOSING_SESSION);
			}
			session.flush();
			session.close();
		}
	}

	/**
	 * Returns the list of the locationCategories based on the conditions
	 * specified in searchListCriteria. It checks for the given values equality,
	 * non-equality, greater, less, like, ...etc. operations.
	 *
	 * @param searchCriteria
	 *            the search criteria
	 *
	 * @return the list
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static List<LocationCategories> getList(
			SearchListCriteria searchCriteria) throws HibernateException {
		logger.info(GET_LIST_WITH_SEARCH_LIST_CRITERIA);
		Session session = HibernateUtil.getSession();
		Criteria criteria = session.createCriteria(LocationCategories.class);
		String orderBY = searchCriteria.getOrderBy();
		if (orderBY != null) {
			if (searchCriteria.isAsc()) {
				criteria.addOrder(Order.asc(orderBY));
			} else {
				criteria.addOrder(Order.desc(orderBY));
			}
		}
		Set<String> keys = searchCriteria.getAttributeNames();
		Iterator<String> it = keys.iterator();
		String key = null;
		Object[] value = null;
		while (it.hasNext()) {
			key = it.next();
			value = searchCriteria.getAttribute(key);
			if (((String) value[1]).equals(SearchListCriteria.EQ)) {
				criteria.add(Restrictions.eq(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.GE)) {
				criteria.add(Restrictions.ge(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.GT)) {
				criteria.add(Restrictions.gt(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.ILIKE)) {
				criteria.add(Restrictions.ilike(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.ISNOTNULL)) {
				criteria.add(Restrictions.isNotNull(key));
			} else if (((String) value[1]).equals(SearchListCriteria.ISNULL)) {
				criteria.add(Restrictions.isNull(key));
			} else if (((String) value[1]).equals(SearchListCriteria.LE)) {
				criteria.add(Restrictions.le(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.LIKE)) {
				criteria.add(Restrictions.like(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.LT)) {
				criteria.add(Restrictions.lt(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.NE)) {
				criteria.add(Restrictions.ne(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.IN)) {
				criteria.add(Restrictions.in(key, (Object[]) value[0]));
			}
		}
		Transaction transaction = session.beginTransaction();
		List<LocationCategories> list = new ArrayList<LocationCategories>();
		try {
			list = criteria.list();
			transaction.commit();
		} catch (HibernateException e) {
			for (String keyexception : keys) {
				logger.info(keys);
			}
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.flush();
			HibernateUtil.closeSession();
		}
		return list;
	}


	/**
	 * Returns the list of the locationCategories based on the conditions
	 * specified in searchListCriteria. It checks for the given values equality,
	 * non-equality, greater, less, like, ...etc. operations.<br>
	 * By default sort by categoryName in ascending order.
	 *
	 * @param searchCriteria
	 *            the search criteria
	 *
	 * @return the list
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static List<LocationCategories> getSortedList(
			SearchListCriteria searchCriteria) throws HibernateException {
		logger.info(GET_LIST_WITH_SEARCH_LIST_CRITERIA);
		Session session = HibernateUtil.getSession();
		Criteria criteria = session.createCriteria(LocationCategories.class);
		String orderBY = searchCriteria.getOrderBy();
		if (orderBY != null) {
			if (searchCriteria.isAsc()) {
				criteria.addOrder(Order.asc(orderBY));
			} else {
				criteria.addOrder(Order.desc(orderBY));
			}
		}
		criteria.addOrder(Order.asc(AdminConstants.CATEGORYNAME));
		Set<String> keys = searchCriteria.getAttributeNames();
		Iterator<String> it = keys.iterator();
		String key = null;
		Object[] value = null;
		while (it.hasNext()) {
			key = it.next();
			value = searchCriteria.getAttribute(key);
			if (((String) value[1]).equals(SearchListCriteria.EQ)) {
				criteria.add(Restrictions.eq(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.GE)) {
				criteria.add(Restrictions.ge(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.GT)) {
				criteria.add(Restrictions.gt(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.ILIKE)) {
				criteria.add(Restrictions.ilike(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.ISNOTNULL)) {
				criteria.add(Restrictions.isNotNull(key));
			} else if (((String) value[1]).equals(SearchListCriteria.ISNULL)) {
				criteria.add(Restrictions.isNull(key));
			} else if (((String) value[1]).equals(SearchListCriteria.LE)) {
				criteria.add(Restrictions.le(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.LIKE)) {
				criteria.add(Restrictions.like(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.LT)) {
				criteria.add(Restrictions.lt(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.NE)) {
				criteria.add(Restrictions.ne(key, value[0]));
			} else if (((String) value[1]).equals(SearchListCriteria.IN)) {
				criteria.add(Restrictions.in(key, (Object[]) value[0]));
			}
		}
		Transaction transaction = session.beginTransaction();
		List<LocationCategories> list = new ArrayList<LocationCategories>();
		try {
			list = criteria.list();
			transaction.commit();
		} catch (HibernateException e) {
			for (String keyexception : keys) {
				logger.info(keys);
			}
			throw e;
		} finally {
			logger.info(SESSION_CLOSED);
			session.flush();
			session.close();
		}
		return list;
	}

	/**
	 * Returns the list of breadCruembs to display in the "Top" link of location
	 * categories.
	 *
	 * @param locationCategories
	 *            the location categories
	 *
	 * @return the bread crumb list
	 *
	 * @throws HibernateException
	 *             the hibernate exception
	 */
	public static List<Integer> getBreadCrumbList(
			LocationCategories locationCategories, LocationCategories mainParentLocationCategories) throws HibernateException {
		List<Integer> breadCrumbIdList = new ArrayList<Integer>();
		StringBuffer selectQueryBuffer = new StringBuffer();
		Session session = HibernateUtil.getSession();
		Transaction transaction = session.beginTransaction();
		Object[] object;
		try {
			selectQueryBuffer.append(SELECT);
			for (int index = 0; index < locationCategories.getLevel(); index++) {
				selectQueryBuffer.append(T);
				selectQueryBuffer.append(index + 1);
				selectQueryBuffer.append(DOT);
				selectQueryBuffer.append(CATEGORYID);
				selectQueryBuffer.append(BLANK);
				selectQueryBuffer.append(AS);
				selectQueryBuffer.append(LEV.intern());
				selectQueryBuffer.append(index + 1);
				if (index < locationCategories.getLevel() - 1)
					selectQueryBuffer.append(COMMA);
			}
			selectQueryBuffer.append(BREAD_CRUMB_QUERY1);
			int indexJoin;
			for (indexJoin = 0; indexJoin < locationCategories.getLevel(); indexJoin++) {
				selectQueryBuffer.append(BREAD_CRUMB_QUERY2);
				selectQueryBuffer.append(indexJoin + 2);
				selectQueryBuffer.append(ON);
				selectQueryBuffer.append(T);
				selectQueryBuffer.append(indexJoin + 2);
				selectQueryBuffer.append(DOT);
				selectQueryBuffer.append(PARENTCATEGORYID);
				selectQueryBuffer.append(BLANK);
				selectQueryBuffer.append(EQUALS);
				selectQueryBuffer.append(T);
				selectQueryBuffer.append(indexJoin + 1);
				selectQueryBuffer.append(DOT);
				selectQueryBuffer.append(CATEGORYID);
				if (locationCategories.getLevel() == 2)
					indexJoin++;
			}
			String categoryName = locationCategories.getCategoryName().replaceAll("'".intern(), "\\\\'".intern());
			selectQueryBuffer.append(BREAD_CRUMB_QUERY3);
			selectQueryBuffer.append(mainParentLocationCategories
					.getCategoryName());
			selectQueryBuffer.append(QUOTE_AND);
			selectQueryBuffer.append(T);
			selectQueryBuffer.append(indexJoin);
			selectQueryBuffer.append(DOT);
			selectQueryBuffer.append(CATEGORYNAME_EQULAS);
			selectQueryBuffer.append(categoryName);
			selectQueryBuffer.append(QUOTE_SEMICOLON);
			Query query = session.createSQLQuery(selectQueryBuffer.toString());
			try {
				object = (Object[]) query.list().get(0);
			} catch (Exception e) {
				object = new Object[] { query.list().get(0) };
			}
			transaction.commit();
		} catch (HibernateException e) {
			transaction.rollback();
			logger.error("Error in getting list for breadcrumb.".intern());
			throw e;
		}
		for (int index = 0; index < object.length; index++) {
			breadCrumbIdList.add(Integer.parseInt(object[index].toString()));
			/*locationCategoriesList.add(get(Integer.parseInt(object[index]
					.toString())));*/
		}
		return breadCrumbIdList;
	}

	/**
	 * Gives the list to display as tree structure for delete confirmation.
	 *
	 * @param categories
	 *            the categories
	 * @param index
	 *            the index
	 * @param list
	 *            the list
	 *
	 * @return the list< string>
	 */

	public static List<LocationCategories> getListToDelete(LocationCategories tempLocationCategories, List<LocationCategories> resultList){
        SearchListCriteria searchListCriteria = new SearchListCriteria();
        searchListCriteria.setAttribute(AdminConstants.LOCATIONCATEGORIES,
                 new Object[] { tempLocationCategories, SearchListCriteria.EQ });

        List<LocationCategories> list = getList(searchListCriteria);
        int totalSize = list.size();

        if(totalSize > 0){
            for (Iterator<LocationCategories> iterator = list.iterator(); iterator.hasNext();) {
                 LocationCategories locationCategories = (LocationCategories) iterator.next();

                 if(locationCategories.getCategoryId() == locationCategories.getLocationCategories().getCategoryId()){
                      if(iterator.hasNext()){
                           locationCategories = (LocationCategories) iterator.next();
                      }else{
                           break;
                      }
                      resultList.add(locationCategories);
                      getListToDelete(tempLocationCategories, resultList);
                 }
            }
        }
        return resultList;
   }

	public static List<String> show(LocationCategories categories,
			String index, List<String> list) {
		if (categories.getLocationCategorieses().size() > 0) {
			for (Iterator iterator = categories.getLocationCategorieses()
					.iterator(); iterator.hasNext();) {
				LocationCategories type = (LocationCategories) iterator.next();
				if (type.getCategoryId() == type.getLocationCategories()
						.getCategoryId()) {
					if (iterator.hasNext()) {
						type = (LocationCategories) iterator.next();
					} else {
						break;
					}
				}
				String display = index + type.getCategoryName() + "("
						+ (type.getLocationCategorieses().size()) + ")";
				list.add(display);
				show(type, "---" + index, list);
				logger.info(" display -- " + display);
			}
		}
		return list;
	}

	public static List<LocationCategories> getParentList(){
		List<LocationCategories> tempLocationCategoriesList=(List<LocationCategories>) ActionContext.getContext().getApplication().get(AdminConstants.LOCATIONCATEGORIES);
		List<LocationCategories> tempList=new ArrayList<LocationCategories>();
		for (Iterator iterator = tempLocationCategoriesList.iterator(); iterator.hasNext();) {
			LocationCategories tempLocationCategories = (LocationCategories) iterator.next();
			if (tempLocationCategories.getLevel() == AdminConstants.ONE_NUMBER){
				tempList.add(tempLocationCategories);
			}
		}
		return tempList;
	}

	public static List<LocationCategories> removeParentfromSet(List<LocationCategories> list , List<LocationCategories> newlist ){
		List<LocationCategories> parentList = getParentList();
		for (Iterator iterator = parentList.iterator(); iterator.hasNext();) {
			LocationCategories generalCategories = (LocationCategories) iterator.next();

		}
		return parentList;
	}

	public static LocationCategories removeParentfromSet(LocationCategories categories ) {
		if(categories.getLocationCategorieses().size()>0){
			Set<LocationCategories> set =new HashSet<LocationCategories>(categories.getLocationCategorieses());
			for (LocationCategories category : categories.getLocationCategorieses()) {
				if(category.getCategoryId()== categories.getCategoryId()){
					set.remove(category);
				}else if(categories.getLocationCategorieses().size()>0){
					removeParentfromSet(category);
				}
				categories.setLocationCategorieses(set);
			}
		}
		return categories;
	}

	/**
	 * The main method.
	 *
	 * @param args
	 *            the arguments
	 */
	public static void main(String[] args) {
		String str = "Cote d'Ivoire";
	    String result = str.replaceAll("'", "\\\\'");
	    System.out.println(result);
		/*LocationCategories locationCategories = get(1);
		List<LocationCategories> list = new ArrayList<LocationCategories>();
		list.add(locationCategories);
		list = getListToDelete(locationCategories, list);
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			LocationCategories type = (LocationCategories) iterator
					.next();
			System.out.println("LocationCategory Name :- "+ type.getCategoryName());
		}*/

	}
}